﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class BaojiaService
    {
        #region 新增
        /// <summary>
        /// 新增一条报价记录
        /// </summary>
        /// <param name="baojia"></param>
        /// <returns></returns>
        public int InsertBaojia(Baojia baojia)
        {
            string sql = "insert into Baojia(WorkParentId,Number,Customer,";
            sql += "Class,CostType,OtherCostType,Requirement,Sales,Success,MoldRequire,MoldClass,CompleteTime,Ps)";
            sql += "values(@WorkParentId,@Number,@Customer,@Class,@CostType,";
            sql += "@OtherCostType,@Requirement,@Sales,@Success,@MoldRequire,@MoldClass,@CompleteTime,@Ps)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",baojia.WorkParentId),
                new SqlParameter("@Number",baojia.Number),
                new SqlParameter("@Customer",baojia.Customer),
                new SqlParameter("@Class",baojia.Class),
                new SqlParameter("@CostType",baojia.CostType),
                new SqlParameter("@OtherCostType",baojia.OtherCostType),
                new SqlParameter("@Requirement",baojia.Requirement),
                new SqlParameter("@Sales",baojia.Sales),
                new SqlParameter("@Success",baojia.Success),
                new SqlParameter("@MoldRequire",baojia.MoldRequire),
                new SqlParameter("@MoldClass",baojia.MoldClass),
                new SqlParameter("@CompleteTime",baojia.CompleteTime),
                new SqlParameter("@Ps",baojia.Ps)
            };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 新增一条报价Log日志
        /// </summary>
        /// <param name="baojia"></param>
        /// <returns></returns>
        public int InsertBaojiaLog(Baojia baojia)
        {
            string sql = "insert into BaojiaLog(DisplayName,WorkParentId,Number,Customer,";
            sql += "Class,CostType,OtherCostType,Requirement,Sales,Success,MoldRequire,MoldClass,CompleteTime,Ps)";
            sql += "values(@DisplayName,@WorkParentId,@Number,@Customer,@Class,@CostType,";
            sql += "@OtherCostType,@Requirement,@Sales,@Success,@MoldRequire,@MoldClass,@CompleteTime,@Ps)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@DisplayName",baojia.DisplayName),
                new SqlParameter("@WorkParentId",baojia.WorkParentId),
                new SqlParameter("@Number",baojia.Number),
                new SqlParameter("@Customer",baojia.Customer),
                new SqlParameter("@Class",baojia.Class),
                new SqlParameter("@CostType",baojia.CostType),
                new SqlParameter("@OtherCostType",baojia.OtherCostType),
                new SqlParameter("@Requirement",baojia.Requirement),
                new SqlParameter("@Sales",baojia.Sales),
                new SqlParameter("@Success",baojia.Success),
                new SqlParameter("@MoldRequire",baojia.MoldRequire),
                new SqlParameter("@MoldClass",baojia.MoldClass),
                new SqlParameter("@CompleteTime",baojia.CompleteTime),
                new SqlParameter("@Ps",baojia.Ps)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 根据WorkParentId查询报价表中是否有记录
        /// </summary>
        /// <param name="workParentId"></param>
        /// <returns></returns>
        public int GetBaojia(int workParentId)
        {
            string sql = "select count(*) from Baojia where WorkParentId=@WorkParentId";
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) }));
        }
        /// <summary>
        /// 获取报价信息
        /// 需要参数：WorkParentId
        /// </summary>
        /// <param name="workParentId"></param>
        /// <returns></returns>
        public Baojia GetBaojiaInfo(int workParentId)
        {
            string sql = "select * from BaoJia where WorkParentId=@WorkParentId";
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) });
            Baojia objBaojia = null;
            if (objReader.Read())
            {
                objBaojia = new Baojia()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    Number = objReader["Number"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    Class = objReader["Class"].ToString(),
                    CostType = objReader["CostType"].ToString(),
                    OtherCostType = objReader["OtherCostType"] is DBNull ? "" : objReader["OtherCostType"].ToString(),
                    Requirement = Convert.ToDouble(objReader["Requirement"]),
                    Sales = Convert.ToDouble(objReader["Sales"]),
                    Success = Convert.ToChar(objReader["Success"]),
                    MoldRequire = objReader["MoldRequire"] is DBNull ? "" : objReader["MoldRequire"].ToString(),
                    MoldClass = objReader["MoldClass"] is DBNull ? "生产模" : objReader["MoldClass"].ToString(),
                    CompleteTime = objReader["CompleteTime"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["CompleteTime"]),
                    Ps = objReader["Ps"] is DBNull ? "" : objReader["Ps"].ToString()
                };
            }
            objReader.Close();
            return objBaojia;
        }
        /// <summary>
        /// 获取最大Id行的Number值
        /// </summary>
        /// <returns></returns>
        public string GetLastNumber()
        {
            string sql = "select Number from BaoJia where Id=(select Max(Id) from BaoJia)";
            return Convert.ToString(SQLHelper.GetSingleResult(sql));
        }
        /// <summary>
        /// 通过报价单号获得Id
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        public int GetId(string no)
        {
            string sql = "select Id from BaoJia where Number=@No";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@No",no)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));

        }
        /// <summary>
        /// 根据报价单号（范围）和客户查询
        /// </summary>
        /// <param name="startId"></param>
        /// <param name="endId"></param>
        /// <param name="customer"></param>
        /// <returns></returns>
        public List<Baojia> GetBaojiaInfo(int startId, int endId, string customer)
        {
            string sql = "select WorkflowStatuses.WorkParentId,CreateTime,StatusNo,CreaterUserName,";
            sql += "DisplayName,Customer,Number,WebName,NodeCount,StatusDefine";
            sql += " from WorkflowStatuses";
            sql += " inner join BaoJia on WorkflowStatuses.WorkParentId = BaoJia.WorkParentId";
            sql += " inner join UserInfoes on WorkflowStatuses.CreaterUserName=UserInfoes.UserName";
            sql += " where 1=1";
            //sql += " where Id>=@Id1 and Id<=@Id2";
            List<SqlParameter> param = new List<SqlParameter>()
            {
                new SqlParameter("@Id1",startId)
            };
            if (endId == -1) { sql += " and Id=@Id1"; }
            else
            {
                sql += " and Id>=@Id1 and Id<=@Id2";
                param.Add(new SqlParameter("@Id2", endId));
            }
            if (customer != "全部")
            {
                sql += " and Customer=@Customer";
                param.Add(new SqlParameter("@Customer", customer));
            }
            List<Baojia> list = new List<Baojia>();
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            while (objReader.Read())
            {
                list.Add(new Baojia()
                    {
                        WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                        Customer = objReader["Customer"].ToString(),
                        Number = objReader["Number"].ToString(),
                        WorkParent = new WorkflowStatus()
                        {
                            CreateTime = Convert.ToDateTime(objReader["CreateTime"]),
                            StatusNo = Convert.ToInt32(objReader["StatusNo"]),
                            WebName = objReader["WebName"].ToString(),
                            NodeCount = Convert.ToInt32(objReader["NodeCount"]),
                            StatusDefine = Convert.ToInt32(objReader["StatusDefine"])
                        },
                        DisplayName = objReader["DisplayName"].ToString()
                    });
            }
            objReader.Close();
            return list;
        }
        #endregion

        #region 更新
        public int UpdateBaojia(Baojia baojia)
        {
            string sql = "update BaoJia set Customer=@Customer,Class=@Class,";
            sql += "CostType=@CostType,OtherCostType=@OtherCostType,Requirement=@Requirement,Sales=@Sales,Success=@Success,MoldRequire=@MoldRequire,MoldClass=@MoldClass,CompleteTime=@CompleteTime,Ps=@Ps";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Customer",baojia.Customer),
                new SqlParameter("@Class",baojia.Class),
                new SqlParameter("@CostType",baojia.CostType),
                new SqlParameter("@OtherCostType",baojia.OtherCostType),
                new SqlParameter("@Requirement",baojia.Requirement),
                new SqlParameter("@Sales",baojia.Sales),
                new SqlParameter("@Success",baojia.Success),
                new SqlParameter("@WorkParentId",baojia.WorkParentId),
                new SqlParameter("@MoldRequire",baojia.MoldRequire),
                new SqlParameter("@MoldClass",baojia.MoldClass),
                new SqlParameter("@CompleteTime",baojia.CompleteTime),
                new SqlParameter("@Ps",baojia.Ps)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion
    }
}
